package com.allen.service;

import com.allen.bean.Student;
import com.allen.bean.User;
import com.allen.utils.JdbcUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Operation {
    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public void updatetime(int id) {
        connection = JdbcUtils.getConnect();
        String sql = "update user set logintime = now() WHERE id = " + id;
        try {
            statement = connection.createStatement();
            statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    public int login(String username, String pwd) {
        connection = JdbcUtils.getConnect();
        String sql = "SELECT * FROM user WHERE username='" + username + "' and pwd='" + pwd + "'";
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                int id = resultSet.getInt("id");
                return id;
            } else {
                return 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public Student findById(Integer id) {
        Student user = new Student();
        connection = JdbcUtils.getConnect();
        String sql = "select * from student where sid=" + id;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                user.setSid(resultSet.getInt(1));
                user.setSname(resultSet.getString(2));
                user.setSage(resultSet.getInt(3));
                user.setSsex(resultSet.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    public List<Student> getAll() {
        List<Student> starry = new ArrayList<>();
        String sql = "SELECT * FROM student;";

        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int sid = resultSet.getInt(1);
                String sname = resultSet.getString(2);
                int sage = resultSet.getInt(3);
                String ssex = resultSet.getString(4);
                Student student = new Student(sid, sname, sage, ssex);
                starry.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return starry;
    }

    public void addUser(HttpServletRequest req, HttpServletResponse resp) {
        try {
            req.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        String sname = req.getParameter("sname");
        int sage = Integer.valueOf(req.getParameter("sage"));
        String ssex = req.getParameter("ssex");

        String sql = "insert into student (sname,sage,ssex) values(?,?,?)";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, sname);
            preparedStatement.setInt(2, sage);
            preparedStatement.setString(3, ssex);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void updateUser(HttpServletRequest req, HttpServletResponse resp) {
        try {
            req.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        int sid = Integer.parseInt(req.getParameter("sid"));
        String sname = req.getParameter("sname");
        int sage = Integer.valueOf(req.getParameter("sage"));
        String ssex = req.getParameter("ssex");
        String sql = "UPDATE student SET sname=?,sage=?,ssex=? WHERE sid=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, sname);
            preparedStatement.setInt(2, sage);
            preparedStatement.setString(3, ssex);
            preparedStatement.setInt(4, sid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void deleteUser(Integer sid) {
        String sql = "DELETE FROM student WHERE sid=?";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, sid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public User findUserById(Integer id) {
        User user = new User();
        connection = JdbcUtils.getConnect();
        String sql = "select * from user where id=" + id;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                user.setId(resultSet.getInt(1));
                user.setUsername(resultSet.getString(2));
                user.setPwd(resultSet.getString(3));
                user.setLogintime(resultSet.getDate(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    public int valid(String name) {
        connection = JdbcUtils.getConnect();
        String sql = "select * from user where username= '" + name + "'";
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            if (resultSet.next()) {
                return 1;
            } else {
                return 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    public void regist(HttpServletRequest req, HttpServletResponse resp){
        try {
            req.setCharacterEncoding("utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        String username = req.getParameter("username");
        String pwd = req.getParameter("pwd");

        String sql = "insert into user(username,pwd) values(?,?)";
        connection = JdbcUtils.getConnect();
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,pwd);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}